import json
from datetime import datetime
from typing import List, Optional, Dict, Any

from sqlalchemy import and_, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.crud.base import CRUDBase
from app.models.contacts import Contact
from app.models.messages import Message
from app.models.sessions import Session
from app.schemas.messages import MessageCreate, MessageUpdate, MessageQuery


class CRUDMessage(CRUDBase[Message, MessageCreate, MessageUpdate]):
    async def create_with_contact(
        self, db: AsyncSession, *, obj_in: MessageCreate, contact: Optional[Contact] = None
    ) -> Message:
        """
        创建消息，同时更新联系人的最后交互时间
        """
        obj_in_data = obj_in.dict()
        
        # 如果提供了contact，使用contact_id，否则使用obj_in中的contact_id
        if contact:
            obj_in_data["contact_id"] = contact.id
            # 更新联系人的最后交互时间
            contact.last_interaction = datetime.utcnow()
            db.add(contact)
        
        # 构建消息对象
        db_obj = Message(**obj_in_data)
        db.add(db_obj)
        await db.commit()
        await db.refresh(db_obj)
        return db_obj
    
    async def update_pua_detection(
        self, db: AsyncSession, *, message_id: int, pua_score: float, pua_patterns: List[Dict[str, Any]]
    ) -> Optional[Message]:
        """
        更新消息的PUA检测结果
        """
        message = await self.get(db, id=message_id)
        if not message:
            return None
        
        message.pua_score = pua_score
        message.pua_patterns = json.dumps(pua_patterns)
        
        db.add(message)
        await db.commit()
        await db.refresh(message)
        return message
    
    async def get_messages_by_query(
        self, db: AsyncSession, *, query: MessageQuery
    ) -> List[Message]:
        """
        根据查询条件获取消息
        """
        db_query = select(Message)
        
        # 应用过滤条件
        conditions = []
        
        if query.contact_id is not None:
            conditions.append(Message.contact_id == query.contact_id)
        
        if query.session_id is not None:
            conditions.append(Message.session_id == query.session_id)
        
        if query.start_time is not None:
            conditions.append(Message.sent_at >= query.start_time)
        
        if query.end_time is not None:
            conditions.append(Message.sent_at <= query.end_time)
        
        if query.msg_type is not None:
            conditions.append(Message.msg_type == query.msg_type)
        
        if query.direction is not None:
            conditions.append(Message.direction == query.direction)
        
        if query.contains_text is not None:
            conditions.append(Message.content.contains(query.contains_text))
        
        if query.has_pua is not None:
            if query.has_pua:
                conditions.append(Message.pua_score.isnot(None))
            else:
                conditions.append(Message.pua_score.is_(None))
        
        if query.min_pua_score is not None:
            conditions.append(Message.pua_score >= query.min_pua_score)
        
        if conditions:
            db_query = db_query.filter(and_(*conditions))
        
        # 应用分页
        if query.offset > 0:
            db_query = db_query.offset(query.offset)
        
        if query.limit > 0:
            db_query = db_query.limit(query.limit)
        
        # 按时间排序
        db_query = db_query.order_by(Message.sent_at.desc())
        
        # 执行查询
        result = await db.execute(db_query)
        return result.scalars().all()
    
    async def get_messages_with_contact_info(
        self, db: AsyncSession, *, messages: List[Message]
    ) -> List[Dict[str, Any]]:
        """
        获取消息并附加联系人信息
        """
        result = []
        
        for message in messages:
            message_dict = {
                "id": message.id,
                "contact_id": message.contact_id,
                "session_id": message.session_id,
                "direction": message.direction,
                "msg_type": message.msg_type,
                "content": message.content,
                "sent_at": message.sent_at,
                "status": message.status,
                "extra_data": message.extra_data,
                "pua_score": message.pua_score,
                "pua_patterns": message.pua_patterns,
                "contact_name": "Unknown",
                "contact_relationship": None
            }
            
            # 如果有联系人ID，查询联系人信息
            if message.contact_id:
                contact_query = select(Contact.nickname, Contact.relationship_type).where(
                    Contact.id == message.contact_id
                )
                contact_result = await db.execute(contact_query)
                contact_info = contact_result.first()
                
                if contact_info:
                    message_dict["contact_name"] = contact_info[0]
                    message_dict["contact_relationship"] = contact_info[1]
            
            result.append(message_dict)
        
        return result


message = CRUDMessage(Message)